﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using System.Globalization;

namespace DataBase.Programa
{
    public class BDPrograma
    {
        const string conexion = "Data Source=.\\sqlexpress; Initial Catalog=JCI; Integrated Security=true";
        public static Model.Programa CerrarPrograma(Model.Programa programa)
        {
            SqlConnection conne = null;
            if (programa != null)
            {
                try
                {
                    conne = new SqlConnection(conexion);
                    SqlCommand change = conne.CreateCommand();
                    change.CommandText = "UPDATE [Programa] SET Estado = @status Where CodigoPrograma=@codigoprograma";
                    change.Parameters.AddWithValue("status", false);
                    change.Parameters.AddWithValue("codigoprograma", programa.CodigoPrograma);
                    conne.Open();
                    change.ExecuteNonQuery();
                    programa.Visible = false;
                }
                catch (Exception)
                {
                    
                    throw;
                }
 
            }
            return programa;
            
        }
        public static void ProgramaEvaluado(Model.Programa programa)
        {
            SqlConnection conne = null;
            if (programa != null)
            {
                try
                {
                    conne = new SqlConnection(conexion);
                    SqlCommand change = conne.CreateCommand();
                    change.CommandText = "UPDATE [Programa] SET Evaluado = @status Where CodigoPrograma=@codigoprograma";
                    change.Parameters.AddWithValue("status", true);
                    change.Parameters.AddWithValue("codigoprograma", programa.CodigoPrograma);
                    conne.Open();
                    change.ExecuteNonQuery();
                }
                catch (Exception)
                {

                    throw;
                }

            }

        }
        public static Model.Programa CrearPrograma(Model.Programa programa)
        {
            if (programa != null)
            {
                try
                {

                    SqlConnection canal = new SqlConnection(conexion);
                    SqlCommand comando = canal.CreateCommand();
                    comando.CommandText = "Insert Into Programa(Nombre,CIDirectora,FechaInicio,FechaFin,Area,Estado,CICreadoPor,Evaluado) Values (@nombre,@directora,@fechainicio,@fechafin,@area,@estado,@creado,@eva)";
                    comando.Parameters.AddWithValue("nombre", programa.Nombre);
                    comando.Parameters.AddWithValue("directora", programa.CIDirectora);
                    comando.Parameters.AddWithValue("fechainicio", programa.FechaInicio);
                    comando.Parameters.AddWithValue("fechafin", programa.FechaFin);
                    comando.Parameters.AddWithValue("area", programa.Area);
                    comando.Parameters.AddWithValue("estado", true);
                    comando.Parameters.AddWithValue("creado", programa.CreadoPor);
                    comando.Parameters.AddWithValue("eva",false);
                    SqlCommand getID = canal.CreateCommand();
                    getID.CommandText = "SELECT @@Identity";
                    canal.Open();
                    comando.ExecuteNonQuery();
                    programa.CodigoPrograma = Convert.ToInt64(getID.ExecuteScalar(), CultureInfo.CurrentCulture);

                }
                catch (Exception)
                {
                    throw;
                }
                
            }
            return programa;
        }
        public static Collection<Model.Programa> ProgramasCategoria(String area, long id, long idPresi)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [PROGRAMA] WHERE Area=@area";
                cmd.Parameters.AddWithValue("area", area);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }

                Collection<Model.Programa> list = new Collection<Model.Programa>();

                while (reader.Read())
                {
                    Model.Programa programa = new Model.Programa();
                    programa.CodigoPrograma = Int64.Parse(reader["CodigoPrograma"].ToString());
                    programa.Area = reader["Area"].ToString();
                    programa.CIDirectora = Int64.Parse(reader["CIDirectora"].ToString());
                    programa.Estado = bool.Parse(reader["Estado"].ToString());
                    programa.FechaFin = DateTime.Parse(reader["FechaFin"].ToString());
                    programa.FechaInicio = DateTime.Parse(reader["FechaInicio"].ToString());
                    programa.Nombre = reader["Nombre"].ToString();
                    programa.CreadoPor = Int64.Parse(reader["CICreadoPor"].ToString());
                    programa.Evaluado = bool.Parse(reader["Evaluado"].ToString());
                    list.Add(programa);
                    if (id == programa.CIDirectora || id == programa.CreadoPor || id == idPresi)
                        programa.Visible = true;
                    else
                        programa.Visible = false;
                }
                return list;
            }
            catch (SqlException ex)
            {
                throw new Exception("Error al obtener programa", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Model.Programa ProgramasIguales(String nombre, DateTime fechaIni)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [PROGRAMA] WHERE Nombre=@nom and FechaInicio=@fecini";
                cmd.Parameters.AddWithValue("nom", nombre);
                cmd.Parameters.AddWithValue("fecini",fechaIni);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }


                Model.Programa programa = new Model.Programa();
                while (reader.Read())
                {

                    programa.CodigoPrograma = Int64.Parse(reader["CodigoPrograma"].ToString());
                    programa.Area = reader["Area"].ToString();
                    programa.CIDirectora = Int64.Parse(reader["CIDirectora"].ToString());
                    programa.Estado = bool.Parse(reader["Estado"].ToString());
                    programa.FechaFin = DateTime.Parse(reader["FechaFin"].ToString());
                    programa.FechaInicio = DateTime.Parse(reader["FechaInicio"].ToString());
                    programa.Nombre = reader["Nombre"].ToString();

                }
                return programa;
            }
            catch (SqlException ex)
            {
                throw new Exception("Error al obtener programa", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Model.Programa ProgramasPorCodigo(long id)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [PROGRAMA] WHERE CodigoPrograma=@id";
                cmd.Parameters.AddWithValue("id", id);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }


                Model.Programa programa = new Model.Programa();
                while (reader.Read())
                {
                    
                    programa.CodigoPrograma = Int64.Parse(reader["CodigoPrograma"].ToString());
                    programa.Area = reader["Area"].ToString();
                    programa.CIDirectora = Int64.Parse(reader["CIDirectora"].ToString());
                    programa.Estado = bool.Parse(reader["Estado"].ToString());
                    programa.FechaFin = DateTime.Parse(reader["FechaFin"].ToString());
                    programa.FechaInicio = DateTime.Parse(reader["FechaInicio"].ToString());
                    programa.Nombre = reader["Nombre"].ToString();
                    programa.CreadoPor = Int64.Parse(reader["CICreadoPor"].ToString());
                    
                }
                return programa;
            }
            catch (SqlException ex)
            {
                throw new Exception("Error al obtener programa", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static string AreaporNum(int num)
        {
            SqlConnection conn = null;
            string area = null;
            try
            {
                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Area] where Num=@num";
                cmd.Parameters.AddWithValue("num", num);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    area = reader["Area"].ToString();

                }
                return area;
            }
            catch (SqlException ex)
            {
                throw new Exception("Error al obtener area", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Model.Programa> DevolverTodosLosProgramas(long id,long idPresi)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [PROGRAMA]";
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                
                Collection<Model.Programa> list = new Collection<Model.Programa>();
                
                while (reader.Read())
                {
                    Model.Programa programa = new Model.Programa();
                    programa.CodigoPrograma=Int64.Parse(reader["CodigoPrograma"].ToString());
                    programa.Area = reader["Area"].ToString();
                    programa.CIDirectora = Int64.Parse(reader["CIDirectora"].ToString());
                    programa.Estado = bool.Parse(reader["Estado"].ToString());
                    programa.FechaFin = DateTime.Parse(reader["FechaFin"].ToString());
                    programa.FechaInicio = DateTime.Parse(reader["FechaInicio"].ToString());
                    programa.Nombre = reader["Nombre"].ToString();
                    programa.CreadoPor = Int64.Parse(reader["CICreadoPor"].ToString());
                    programa.Evaluado = bool.Parse(reader["Evaluado"].ToString());
                    if (id == programa.CIDirectora || id==programa.CreadoPor ||id==idPresi)
                        programa.Visible = true;
                    else
                        programa.Visible = false;
                    list.Add(programa);
                }
                return list;
            }
            catch (SqlException ex)
            {
                throw new Exception("Error al obtener programa", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static void ModificarPrograma(Model.Programa programa)
        {
            if (programa != null)
            {
                try
                {

                    SqlConnection canal = new SqlConnection(conexion);
                    SqlCommand comando = canal.CreateCommand();
                    comando.CommandText = "Update [Programa] set Nombre=@nombre,CIDirectora=@directora,FechaInicio=@fechainicio,FechaFin=@fechafin,Area=@area Where CodigoPrograma=@codigo";
                    comando.Parameters.AddWithValue("nombre", programa.Nombre);
                    comando.Parameters.AddWithValue("directora", programa.CIDirectora);
                    comando.Parameters.AddWithValue("fechainicio", programa.FechaInicio);
                    comando.Parameters.AddWithValue("fechafin", programa.FechaFin);
                    comando.Parameters.AddWithValue("area", programa.Area);
                    comando.Parameters.AddWithValue("codigo", programa.CodigoPrograma);
                    canal.Open();
                    comando.ExecuteNonQuery();

                }
                catch (Exception)
                {
                    throw;
                }

            }
           
        }

        public static Collection<Model.Programa> DevolverTodosLosProgramasCerrados(long id, long idPresi)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [PROGRAMA]";
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }

                Collection<Model.Programa> list = new Collection<Model.Programa>();

                while (reader.Read())
                {
                    Model.Programa programa = new Model.Programa();
                    programa.CodigoPrograma = Int64.Parse(reader["CodigoPrograma"].ToString());
                    programa.Area = reader["Area"].ToString();
                    programa.CIDirectora = Int64.Parse(reader["CIDirectora"].ToString());
                    programa.Estado = bool.Parse(reader["Estado"].ToString());
                    programa.FechaFin = DateTime.Parse(reader["FechaFin"].ToString());
                    programa.FechaInicio = DateTime.Parse(reader["FechaInicio"].ToString());
                    programa.Nombre = reader["Nombre"].ToString();
                    programa.CreadoPor = Int64.Parse(reader["CICreadoPor"].ToString());
                    programa.Evaluado = bool.Parse(reader["Evaluado"].ToString());
                    if ((id == programa.CIDirectora || id == programa.CreadoPor || id == idPresi))
                        programa.Visible = true;
                    else
                        programa.Visible = false;
                    if (programa.Estado)
                    list.Add(programa);
                }
                return list;
            }
            catch (SqlException ex)
            {
                throw new Exception("Error al obtener programa", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }    
        }

        public static Collection<Model.Programa> ProgramasCategoriaCerrado(String area, long id, long idPresi)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(conexion);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [PROGRAMA] WHERE Area=@area";
                cmd.Parameters.AddWithValue("area", area);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }

                Collection<Model.Programa> list = new Collection<Model.Programa>();

                while (reader.Read())
                {
                    Model.Programa programa = new Model.Programa();
                    programa.CodigoPrograma = Int64.Parse(reader["CodigoPrograma"].ToString());
                    programa.Area = reader["Area"].ToString();
                    programa.CIDirectora = Int64.Parse(reader["CIDirectora"].ToString());
                    programa.Estado = bool.Parse(reader["Estado"].ToString());
                    programa.FechaFin = DateTime.Parse(reader["FechaFin"].ToString());
                    programa.FechaInicio = DateTime.Parse(reader["FechaInicio"].ToString());
                    programa.Nombre = reader["Nombre"].ToString();
                    programa.CreadoPor = Int64.Parse(reader["CICreadoPor"].ToString());
                    programa.Evaluado = bool.Parse(reader["Evaluado"].ToString());
                    if (programa.Estado)
                    list.Add(programa);
                    if ((id == programa.CIDirectora || id == programa.CreadoPor || id == idPresi) )
                        programa.Visible = true;
                    else
                        programa.Visible = false;
                }
                return list;
            }
            catch (SqlException ex)
            {
                throw new Exception("Error al obtener programa", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
    }
}
